package com.lord.rowcopy.excel;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

import com.lord.rowcopy.util.DataHelper;

import cn.hutool.core.util.ObjectUtil;

public class BusinessSheet {

    private HSSFWorkbook wb;
    private Sheet sheet;
    private int index = 0;

    private static BusinessSheet bs = new BusinessSheet();

    public static BusinessSheet getInstance() {
        return bs;
    }

    @SuppressWarnings("unchecked")
    public File exportFile(Map<String, Object> map, String template, String tmpPath, String fileName) {

        if (map == null) {
            throw new RuntimeException();
        }

        try {
            wb = new HSSFWorkbook(TemplateFileUtil.getTemplates(template));
            sheet = wb.getSheetAt(0);

            // 如果这行没有了，整个公式都不会有自动计算的效果的
            sheet.setForceFormulaRecalculation(true);

            for (Row row : sheet) {
                for (Cell c : row) {
                    String str = c.getStringCellValue().trim();

                    // 处理特殊行（多行数值）
                    if ("股东名称".equals(str) || "投资金额（万元）".equals(str) || "比例".equals(str)) {
                        continue;
                    }
                    if ("保证人情况".equals(str)) {
                        break;
                    }

                    // 替换模板变量
                    if (str.startsWith("#")) {
                        if (map.containsKey(str.substring(1))) {
                            c.setCellValue(ObjectUtil.isNotEmpty(map.get(str.substring(1)))
                                    ? map.get(str.substring(1)).toString()
                                    : "");
                        }
                    }
                }
            }

            Map<Integer, String> companyPartnerKeyMap = new HashMap<>();
            List<Map<String, String>> companyPartnerList = new ArrayList<>();

            // 处理股东信息集合
            if (ObjectUtil.isNotEmpty(map.get("companyPartnerList"))) {
                companyPartnerList = (List<Map<String, String>>) map.get("companyPartnerList");
            }

            // 动态填充模版变量
            for (Row row : sheet) {
                for (Cell c : row) {
                    String str = c.getStringCellValue().trim();

                    // 获取模板中的变量
                    if (str.startsWith("#")) {
                        companyPartnerKeyMap.put(c.getColumnIndex(), str);
                    }

                    if (c.getRowIndex() <= getNextRowIndex(sheet, "企业名称")) {
                        continue;
                    }

                    if (c.getRowIndex() >= getNextRowIndex(sheet, "企业名称")
                            && c.getRowIndex() <= getNextRowIndex(sheet, "当前项目经历")) {
                        if (ObjectUtil.isNotEmpty(companyPartnerList)) {
                            if (c.getColumnIndex() == 4 || c.getColumnIndex() == 6 || c.getColumnIndex() == 7) {
                                c.setCellValue(companyPartnerKeyMap.get(c.getColumnIndex()));
                                index++;
                            }

                            if (index == (companyPartnerList.size() * 2 - 1)) {
                                break;
                            }
                        }
                    } else {
                        index = 0;
                        break;
                    }
                }
            }

            // 模版变量赋值
            for (Row row : sheet) {
                for (Cell c : row) {
                    String str = c.getStringCellValue().trim();

                    // 替换模板变量
                    if (str.startsWith("#")) {
                        for (Map<String, String> companyPartner : companyPartnerList) {
                            if (companyPartner.containsKey(str.substring(1))) {
                                c.setCellValue(ObjectUtil.isNotEmpty(companyPartner.get(str.substring(1)))
                                        ? companyPartner.get(str.substring(1)).toString()
                                        : "");
                                index++;
                                break;
                            }
                        }

                        // 赋值一次之后，移除已使用过的变量
                        if (companyPartnerList.size() > 1) {
                            if (index == companyPartnerList.get(0).size()) {
                                companyPartnerList.remove(0);
                                index = 0;
                            }
                        }
                    }
                }

                if (row.getRowNum() > getNextRowIndex(sheet, "当前项目经历")) {
                    index = 0;
                    break;
                }
            }

            // 处理保证人情况集合
            List<Map<String, Object>> guarantorList = (List<Map<String, Object>>) map.get("guarantorList");
            if (DataHelper.isEmpty(guarantorList)) {

                /**
                 * 此处代码特殊处理，当保证人信息不存在的时候，利用空字段填充模板
                 */
                Map<String, Object> guarantorMap = new HashMap<>();
                guarantorMap.put("name", "");
                guarantorMap.put("cardNo", "");
                guarantorMap.put("avgAmount", "");
                guarantorMap.put("telephone", "");
                guarantorMap.put("job", "");
                guarantorMap.put("familyAddress", "");
                guarantorMap.put("houseTotalNum", "");
                guarantorMap.put("houseTotalPrice", "");
                guarantorMap.put("carTotalNum", "");
                guarantorMap.put("carTotalPrice", "");
                guarantorMap.put("otherDescription", "");
                guarantorList.add(guarantorMap);
            }

            // 动态修改模板并赋值
            copyTemplate(guarantorList, "保证人（可多填）", "房产抵押情况", 4);

            // 处理房产抵押情况集合
            List<Map<String, Object>> housePledgeList = (List<Map<String, Object>>) map.get("housePledgeList");
            if (DataHelper.isEmpty(housePledgeList)) {

                /**
                 * 此处代码特殊处理，当抵押房产信息不存在的时候，利用空字段填充模板
                 */
                Map<String, Object> housePledgeMap = new HashMap<>();
                housePledgeMap.put("ownerName", "");
                housePledgeMap.put("ownerIdCardNo", "");
                housePledgeMap.put("ownerTelephone", "");
                housePledgeMap.put("address", "");
                housePledgeMap.put("maritalStatus", "");
                housePledgeMap.put("houseArea", "");
                housePledgeMap.put("buyTotalPrice", "");
                housePledgeMap.put("makeTime", "");
                housePledgeMap.put("nowStatus", "");
                housePledgeMap.put("assessUnitPrice", "");
                housePledgeMap.put("assessTotalPrice", "");
                housePledgeMap.put("suggestLoanAmount", "");
                housePledgeMap.put("loanRatio", "");
                housePledgeMap.put("otherDescription", "");
                housePledgeList.add(housePledgeMap);
            }

            // 动态修改模板并赋值
            copyTemplate(housePledgeList, "抵押物（可多填）", "车辆质押情况", 5);

            // 处理房产抵押情况集合
            List<Map<String, Object>> carPledgeList = (List<Map<String, Object>>) map.get("carPledgeList");
            if (DataHelper.isEmpty(carPledgeList)) {

                /**
                 * 此处代码特殊处理，当质押车辆信息不存在的时候，利用空字段填充模板
                 */
                Map<String, Object> carPledgeMap = new HashMap<>();
                carPledgeMap.put("ownerName", "");
                carPledgeMap.put("idCardNo", "");
                carPledgeMap.put("distance", "");
                carPledgeMap.put("telephone", "");
                carPledgeMap.put("carBrandModel", "");
                carPledgeMap.put("carEngineNo", "");
                carPledgeMap.put("buyTotalPrice", "");
                carPledgeMap.put("buyTime", "");
                carPledgeMap.put("carPlateNo", "");
                carPledgeMap.put("internetPrice", "");
                carPledgeMap.put("assessTotalPrice", "");
                carPledgeMap.put("suggestLoanAmount", "");
                carPledgeMap.put("loanRatio", "");
                carPledgeMap.put("otherDescription", "");
                carPledgeList.add(carPledgeMap);
            }

            // 动态修改模板并赋值
            copyTemplate(carPledgeList, "质押物（可多填）", "贷款顾问意见", 5);

            if (DataHelper.isEmpty(fileName)) {
                fileName = System.currentTimeMillis() + "";
            }

            // 修改模板内容导出新模板
            try (FileOutputStream fos = new FileOutputStream(tmpPath + "/" + fileName)) {
                wb.write(fos);

                fos.flush();
                fos.close();

                File file = new File(tmpPath + "/" + fileName);
                return file;
            }
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException("读取模板不存在！请检查");
        }
    }

    /**
     * 获取特殊行下一行坐标
     * @param sheet
     * @param keyword
     * @return
     */
    private Integer getNextRowIndex(Sheet sheet, String keyword) {
        int beginIndex = 0;
        for (Row row : sheet) {
            for (Cell c : row) {
                String str = c.getStringCellValue().trim();
                if (str.equals(keyword)) {
                    beginIndex = c.getRowIndex() + 1;
                    break;
                }
            }
        }

        return beginIndex;
    }

    /**
     * 动态修改模板并赋值
     * @param dataList 数据集合（外部判空）
     * @param startRowKeyword 开始行关键字
     * @param endIndexKeyword 结束行关键字
     * @param copyRowNum 需要拷贝的行数 
     */
    private void copyTemplate(List<Map<String, Object>> dataList, String startRowKeyword, String endIndexKeyword,
            Integer copyRowNum) {

        if (dataList.size() > 1) {
            int startIndex = getNextRowIndex(sheet, startRowKeyword);
            int endIndex = getNextRowIndex(sheet, endIndexKeyword);

            for (int i = 1; i < dataList.size(); i++) {
                sheet.shiftRows(endIndex - 1, sheet.getLastRowNum(), copyRowNum, true, false);
                for (int j = 0; j < copyRowNum; j++) {
                    int fromRow = startIndex;
                    int toRow = (endIndex - 1);
                    ExcelUtil.getInstance().copyRow(wb, sheet.getRow(fromRow + j), sheet.getRow(toRow + j), true);
                }
            }
        }

        // 模版变量赋值
        for (Row row : sheet) {
            for (Cell c : row) {
                String str = c.getStringCellValue().trim();

                // 替换模板变量
                if (str.startsWith("#")) {
                    for (Map<String, Object> data : dataList) {
                        if (ObjectUtil.isNotEmpty(data)) {
                            if (data.containsKey(str.substring(1))) {
                                c.setCellValue(ObjectUtil.isNotEmpty(data.get(str.substring(1)))
                                        ? data.get(str.substring(1)).toString()
                                        : "");
                                index++;
                                break;
                            }
                        }
                    }

                    // 赋值一次之后，移除已使用过的变量
                    if (dataList.size() >= 1) {
                        if (index == dataList.get(0).size()) {
                            dataList.remove(0);
                            index = 0;
                        }
                    }
                }
            }

            if (row.getRowNum() > getNextRowIndex(sheet, endIndexKeyword)) {
                index = 0;
                break;
            }
        }
    }

}
